package org.finalministry.database.dao;

import geodb.GeoDB;

import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;

import org.finalministry.database.connection.IDatabaseConnection;
import org.finalministry.objects.Area;
import org.finalministry.utils.geo.GeoUtils;

import com.vividsolutions.jts.geom.Geometry;
import com.vividsolutions.jts.io.ParseException;

public class AreaDAO extends BaseDAO {

	public AreaDAO(IDatabaseConnection connection) {
		super(connection);
	}

	/*
	 * CREATE TABLE AREA (ID BIGINT auto_increment, NAME VARCHAR(255) NOT NULL,
	 * DESCRIPTION VARCHAR(1000), GEOMETRY BLOB, PRIMARY KEY (ID));
	 */

	public Long create(Area area) throws Exception {
		System.out.println("Create area");

		String insert = "INSERT INTO AREA (NAME,DESCRIPTION,GEOMETRY,CONGREGATIONID) VALUES(?,?,ST_GeomFromText(?, 4326),?)";
		PreparedStatement stmt = getConnection().getConnection().prepareStatement(insert);

		stmt.setString(1, area.getName());
		stmt.setString(2, area.getDescription());
		stmt.setString(3, GeoDB.gToWKT(area.getGeometry()));
		stmt.setLong(4, area.getCongregationID());

		stmt.executeUpdate();

		stmt.close();

		return lastGeneratedId();

	}

	private Long lastGeneratedId() throws Exception {
		Statement stmt = getConnection().getConnection().createStatement();
		ResultSet result = stmt.executeQuery("SELECT MAX(ID) FROM AREA");

		if (result.next()) {
			return result.getLong(1);
		}
		return null;
	}

	public Area getById(Long id) throws SQLException, ClassNotFoundException, ParseException {

		Statement stmt = getConnection().getConnection().createStatement();
		ResultSet result = stmt.executeQuery("SELECT ID,CONGREGATIONID,NAME,DESCRIPTION,ST_AsText(GEOMETRY) FROM AREA WHERE ID = " + id);

		if (result.next()) {
			Area area = new Area();
			area.setId(result.getLong(1));
			area.setCongregationID(result.getLong(2));
			area.setName(result.getString(3));
			area.setDescription(result.getString(4));
			Geometry geometry = GeoUtils.getPointFromText(result.getString(5));
			area.setGeometry(geometry);

			return area;
		}

		return null;
	}

	public List<Area> getAll() throws SQLException, ClassNotFoundException, ParseException {

		Statement stmt = getConnection().getConnection().createStatement();
		String select = "SELECT ID,CONGREGATIONID,NAME,DESCRIPTION,ST_AsText(GEOMETRY) FROM AREA";

		if (getCongregationID() != null) {
			select += " WHERE congregationID = " + getCongregationID() + " ORDER BY NAME, DESCRIPTION";
		}

		return standardSelectAreaList(select, stmt);
	}

	/**
	 * Convinient method for return a list of contacts via a simple executeQuery
	 * from
	 * 
	 * <pre>
	 * SELECT * FROM CONTACT ...
	 * </pre>
	 * 
	 * @param select
	 * @param stmt
	 * @return
	 * @throws SQLException
	 * @throws ParseException
	 * @throws ClassNotFoundException
	 */
	private List<Area> standardSelectAreaList(String select, Statement stmt) throws SQLException, ClassNotFoundException, ParseException {

		ResultSet result = stmt.executeQuery(select);
		List<Area> allAreas = new ArrayList<Area>();

		if (result.next()) {
			Area area = new Area();
			area.setId(result.getLong(1));
			area.setCongregationID(result.getLong(2));
			area.setName(result.getString(3));
			area.setDescription(result.getString(4));
			Geometry geometry = GeoUtils.getPointFromText(result.getString(5));
			area.setGeometry(geometry);

			allAreas.add(area);
		}

		return allAreas;
	}

	public void update(Area area) throws Exception {

		String insert = "UPDATE AREA SET CONGREGATIONID = ?, NAME = ?, DESCRIPTION = ?, GEOMETRY = ST_GeomFromText(?, 4326) WHERE ID = ?";
		PreparedStatement stmt = getConnection().getConnection().prepareStatement(insert);

		stmt.setLong(1, area.getCongregationID());
		stmt.setString(2, area.getName());
		stmt.setString(3, area.getDescription());
		stmt.setString(4, GeoDB.gToWKT(area.getGeometry()));
		stmt.setLong(5, area.getId());

		stmt.executeUpdate();

		stmt.close();

	}

	public void delete(Area area) throws Exception {

		String delete = "DELETE FROM AREA WHERE ID = " + area.getId();
		Statement stmt = getConnection().getConnection().createStatement();
		stmt.executeUpdate(delete);
		stmt.close();
	}

	public List<Area> getByName(Area area) throws SQLException, ClassNotFoundException, ParseException {

		String select = "SELECT ID,CONGREGATIONID,NAME,DESCRIPTION,ST_AsText(GEOMETRY) FROM AREA WHERE NAME = ?";
		PreparedStatement stmt = getConnection().getConnection().prepareStatement(select);

		stmt.setString(1, area.getName());

		return standardSelectAreaList(select, stmt);
	}
}
